package cn.com.shopec.erp.product.dao.impl;

import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.stereotype.Repository;

import cn.com.shopec.erp.common.orm.impl.HibernateEntityDao;
import cn.com.shopec.erp.common.utils.StringUtil;
import cn.com.shopec.erp.product.dao.ICommodityDao;
import cn.com.shopec.erp.product.entity.Commodity;
import cn.com.shopec.erp.product.vo.CommodityVo;

/**
 * 
 * @ClassName: CommodityDaoImpl1
 * @Description: 商品数据访问层DAO实现类
 * @author: duhui
 * @date 2013-5-29 下午03:51:36
 * 
 */
@Repository
public class CommodityDaoImpl extends HibernateEntityDao<Commodity> implements ICommodityDao {

    @Override
    public Session getCurSession() throws Exception {
        return this.getHibernateTemplate().getSessionFactory().getCurrentSession();
    }
    
    @Override
    public List CommodityDetail(CommodityVo commodityVo){
    	String params = "";
    	if(StringUtil.isExist(commodityVo.getCommoNo())){
    		params += " and c.commo_no='"+commodityVo.getCommoNo()+"'";
    	}
    	if(StringUtil.isExist(commodityVo.getCommoName())){
    		params += " and c.commo_name like '%"+commodityVo.getCommoName()+"%'";
    	}
    	if(StringUtil.isExist(commodityVo.getProdNo())){
    		params += " and p.prod_no='"+commodityVo.getProdNo()+"'";
    	}
    	if(StringUtil.isExist(commodityVo.getCreateStartDate())){
    		params += " and c.create_time > '"+commodityVo.getCreateStartDate()+"'";
    	}
    	if(StringUtil.isExist(commodityVo.getCreateEndDate())){
    		params += " and c.create_time < '"+commodityVo.getCreateEndDate()+"'";
    	}
    	
    	String sql = "select c.commo_no,c.commo_name,cat.cat_name,b.brand_name,c.commo_type,c.create_time,c.description, ";
    	sql += "p.prod_no,p.prod_bar_code,p.sale_type,p.inventory_type,p.sale_price,p.prod_rate,p.un_sale_price, ";
    	sql += "p.buy_num,p.case_pack_size,p.prod_unit, ";
    	sql += "(select tsvo.option_name from tbl_spec_val_option tsvo left outer join tbl_prod_spec_value tpsv on tsvo.option_no=tpsv.val_option_no where tpsv.prod_no=p.prod_no) as spec_name, ";
    	sql += "sp.sup_name,sp.account,ps.untax_cost_price,ps.tax_cost_price, ";
    	sql += "ps.gross_margin,ps.is_returned_goods ";
    	sql += "from tbl_commodity c ";
    	sql += "left outer join tbl_product p on c.commo_no = p.commo_no  ";
    	sql += "left outer join tbl_prod_supplier ps on p.prod_no = ps.prod_no  ";
    	sql += "left outer join tbl_supplier_info sp on ps.supplier_no = sp.code  ";
    	sql += "left outer join tbl_commodity_cat cat on c.cat_no = cat.cat_no  ";
    	sql += "left outer join tbl_brand b on c.brand_no = b.brand_no ";
    	sql += "where c.delete_flag = 1 and p.delete_flag=1 and ps.delete_flag=1 and c.check_state=1 " + params;
    	sql += "order by c.create_time desc";
    	try {
    		Query query =this.createSQLQuery(sql);
    		return query.list();
		} catch (HibernateException e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
			return null;
		}
		
    }
}
